package com.ifnk.portaljava.hlyt.dao.impl;

import com.ifnk.portaljava.dapWeb.advance.DBAccess;
import com.ifnk.portaljava.hlyt.beans.Module;
import com.ifnk.portaljava.hlyt.beans.Role;
import com.ifnk.portaljava.hlyt.beans.RolePermission;
import com.ifnk.portaljava.hlyt.dao.IRoleJdbcDao;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class RoleJdbcDaoImpl implements IRoleJdbcDao {

	public void addModuleToRoles(int moduleId, String permission, Role role,
			Connection conn) throws Exception {
		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int id = 0;
		try {
			stmt = conn.createStatement();
			String sql = "select max(id) as maxId from role_permission;";
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				id = rs.getInt("maxId") + 1;
			}

			sql = "insert into role_permission(id,role_id,module_id,permission,createtime) values (?,?,?,?,?);";
			System.out.println("sql:" + sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.setInt(2, role.getId());
			pstmt.setInt(3, moduleId);
			pstmt.setString(4, permission);
			pstmt.setDate(5, new Date(Calendar.getInstance().getTime()
					.getTime()));
			pstmt.execute();
		} catch (SQLException e) {
			throw new Exception ("addModuleToRoles error!");
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public List<Role> findAll() throws Exception {
		DBAccess db = new DBAccess();
		ResultSet rs = null;
		List<Role> roleList = null;
		try {
			String sql = "select c.id as id,c.name as name,c.createtime as createtime,c.disabletime as disabletime,c.rpid as rpid,c.permission as permission,"
					+ "m.id as moduleid,m.name as modulename from module m right outer join "
					+ "(select r.id as id,r.name as name,r.createtime as createtime,r.disabletime as disabletime,"
					+ "rp.id as rpid,rp.role_id,rp.permission as permission,rp.module_id as module_id from role r left outer join role_permission rp on r.id=rp.role_id) c "
					+ "on m.id=c.module_id;";
			System.out.println("sql:" + sql);
			rs = db.queryTable(sql);
			roleList = new ArrayList<Role>();
			Role role = null;
			int lastId = -1;
			int thisId = 0;

			while (rs.next()) {
				thisId = rs.getInt("id");
				RolePermission rp = new RolePermission();
				rp.setId(rs.getInt("rpid"));
				rp.setPermission(rs.getString("permission"));
				Module module = new Module();
				module.setId(rs.getInt("moduleid"));
				module.setName(rs.getString("modulename"));
				rp.setModule(module);
				if (lastId != thisId) {
					role = new Role();
					role.setId(rs.getInt("id"));
					role.setName(rs.getString("name"));
					role.setCreatetime(rs.getTimestamp("createtime"));
					role.setDisabletime(rs.getTimestamp("disabletime"));
					role.setCreatetimeString(new SimpleDateFormat(
							"yyyy-MM-dd HH:mm:ss").format(rs
							.getTimestamp("createtime")));
					if (rs.getTimestamp("disabletime") != null) {
						role.setDisabletimeString(new SimpleDateFormat(
								"yyyy-MM-dd HH:mm:ss").format(rs
								.getTimestamp("disabletime")));
					}
					roleList.add(role);
				}
				role.getRolePermissionSet().add(rp);
				if (rs.getString("modulename") != null) {
					if (role.getPermissionString() == "") {
						role.setPermissionString(rs.getString("modulename")
								+ ":" + rs.getString("permission"));
					} else {
						role.setPermissionString(role.getPermissionString()
								+ ";" + rs.getString("modulename") + ":"
								+ rs.getString("permission"));
					}
				}
				lastId = thisId;
			}
		} catch (SQLException e) {
			throw new Exception ("findAll error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return roleList;
	}

	public List findByName(String roleName) throws Exception {
		DBAccess db = new DBAccess();
		String sql = "select id,name from role where name='" + roleName + "'";
		System.out.println("sql:" + sql);
		ResultSet rs = db.queryTable(sql);
		List list = new ArrayList();
		try {
			while (rs.next()) {
				Role role = new Role();
				role.setId(rs.getInt("id"));
				role.setName(rs.getString("name"));
				list.add(role);
			}
		} catch (SQLException e) {
			throw new Exception ("findByName error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	public int save(Role role, Connection conn) throws Exception {
		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int id = 0;
		try {
			String idSql = "select max(id) as maxId from role";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(idSql);
			while (rs.next()) {
				id = rs.getInt("maxId") + 1;
			}
			String sql = "insert into role(id,name,createtime) values(?,?,now())";
			System.out.println("insert sql:" + sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.setString(2, role.getName());
			pstmt.execute();
		} catch (SQLException e) {
			throw new Exception ("save role error!");
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return id;
	}

	public void deleteUserRole(int roleId, Connection conn) throws Exception {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			String sql = "delete from user_role where role_id=" + roleId;
			System.out.println("sql:" + sql);
			stmt.execute(sql);
		} catch (SQLException e) {
			throw new Exception ("deleteUserRole error!");
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public List<Role> findByUserId(int userId) throws Exception {
		DBAccess db = new DBAccess();
		String sql = "select r.id,r.name from role r where r.id in (select role_id from user_role where user_id='"
				+ userId + "')";
		System.out.println("sql:" + sql);
		ResultSet rs = db.queryTable(sql);
		List list = new ArrayList();
		try {
			while (rs.next()) {
				Role role = new Role();
				role.setId(rs.getInt("id"));
				role.setName(rs.getString("name"));
				list.add(role);
			}
		} catch (SQLException e) {
			throw new Exception ("findByUserId error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	public List<Role> findbyRole(Role role) throws Exception {
		DBAccess db = new DBAccess();
		String sql = "select r.id,r.name,r.createtime,r.disabletime from role r";
		String nameString = "";
		String timeString = "";
		System.out.println(role.getName() + ":" + role.getCreatetimeString());
		if (role.getName() != "") {
			nameString = " where r.name like '%" + role.getName() + "%'";
		}

		if (role.getCreatetimeString() != "") {
			if (nameString == "") {
				timeString = " where r.createtime like '%"
						+ role.getCreatetimeString() + "%'";
			} else {
				timeString = " and r.createtime like '%"
						+ role.getCreatetimeString() + "%'";
			}
		}
		sql = sql + nameString + timeString;
		System.out.println("sql:" + sql);
		ResultSet rs = db.queryTable(sql);
		List list = new ArrayList();
		try {
			while (rs.next()) {
				Role r = new Role();
				r.setId(rs.getInt("id"));
				r.setName(rs.getString("name"));
				r.setCreatetime(rs.getTimestamp("createtime"));
				r.setDisabletime(rs.getTimestamp("disabletime"));
				r.setCreatetimeString(new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss").format(rs
						.getTimestamp("createtime")));
				if (rs.getTimestamp("disabletime") != null) {
					r.setDisabletimeString(new SimpleDateFormat(
							"yyyy-MM-dd HH:mm:ss").format(rs
							.getTimestamp("disabletime")));
				}
				list.add(r);
			}
		} catch (SQLException e) {
			throw new Exception ("findbyRole error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	public static void main(String[] args) throws Exception {
		RoleJdbcDaoImpl rd = new RoleJdbcDaoImpl();
		Module module = new Module();
		module.setId(1);
		module.setPermission("read;add");
		List roleList = rd.findByModule(module);
		System.out.println(roleList.size());
		// int count = 0;
		// for (int i = 0; i < roleList.size(); i++) {
		// Role r = (Role) roleList.get(i);
		// // System.out.println(r.getId() + ":" + r.getName() + ":"
		// // + r.getPermissionString() + ":"
		// // + r.getRolePermissionSet().size());
		// System.out.println(r.getId() + ":" + r.getName());
		// count += r.getRolePermissionSet().size();
		// }
	}

	public List findByModule(Module module) throws Exception {

		DBAccess db = new DBAccess();
		String sql = "";
		if (module.getPermission() == "") {
			sql = "select r.id,r.name,r.createtime,r.disabletime from role r,(select role_id from role_permission rp where module_id="
					+ module.getId() + ") a where r.id = a.role_id";
		} else {
			String[] permis = module.getPermission().split(";");
			String permissionSql = "";
			for (int i = 0; i < permis.length; i++) {
				if (permissionSql != "") {
					permissionSql += " and ";
				}
				permissionSql += " rp.permission like '%" + permis[i] + "%'";
			}
			sql = "select r.id,r.name,r.createtime,r.disabletime from role r,(select role_id from role_permission rp where module_id="
					+ module.getId()
					+ " and "
					+ permissionSql
					+ ") a where r.id = a.role_id";
		}
		System.out.println("sql:" + sql);
		ResultSet rs = db.queryTable(sql);
		List list = new ArrayList();
		try {
			while (rs.next()) {
				Role r = new Role();
				r.setId(rs.getInt("id"));
				r.setName(rs.getString("name"));
				r.setCreatetime(rs.getTimestamp("createtime"));
				r.setDisabletime(rs.getTimestamp("disabletime"));
				r.setCreatetimeString(new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss").format(rs
						.getTimestamp("createtime")));
				if (rs.getTimestamp("disabletime") != null) {
					r.setDisabletimeString(new SimpleDateFormat(
							"yyyy-MM-dd HH:mm:ss").format(rs
							.getTimestamp("disabletime")));
				}
				list.add(r);
			}
		} catch (SQLException e) {
			throw new Exception ("findByModule error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	public List<Role> findByUserName(String userName) throws Exception {
		DBAccess db = new DBAccess();
		String sql = "select r.id,r.name,r.createtime,r.disabletime from role r,(select ur.role_id as roleid from user_role ur,(select u.id from user u where u.name like '%"
				+ userName
				+ "%') a where ur.user_id=a.id) b where r.id=b.roleid;";
		System.out.println("sql:" + sql);
		ResultSet rs = db.queryTable(sql);
		List list = new ArrayList();
		try {
			while (rs.next()) {
				Role r = new Role();
				r.setId(rs.getInt("id"));
				r.setName(rs.getString("name"));
				r.setCreatetime(rs.getTimestamp("createtime"));
				r.setDisabletime(rs.getTimestamp("disabletime"));
				r.setCreatetimeString(new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss").format(rs
						.getTimestamp("createtime")));
				if (rs.getTimestamp("disabletime") != null) {
					r.setDisabletimeString(new SimpleDateFormat(
							"yyyy-MM-dd HH:mm:ss").format(rs
							.getTimestamp("disabletime")));
				}
				list.add(r);
			}
		} catch (SQLException e) {
			throw new Exception ("findByUserName error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	public void deleteRole(int roleId, Connection conn) throws Exception {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			String sql = "delete from role where id=" + roleId;
			System.out.println("sql:" + sql);
			stmt.execute(sql);
		} catch (SQLException e) {
			throw new Exception ("delete Role error!");
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
	}

}
